package zz.extjs.impl;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.json.JSONArray;
import org.json.JSONObject;

import zz.extjs.dao.DatasetDAO;
import zz.extjs.dbc.DataBaseConnection;
import zz.extjs.dbc.ParseXML;
import zz.extjs.factory.DAOFactory;

public class DatasetDAOImpl implements DatasetDAO {
	public JSONArray getCosCount(String y_term) throws Exception {
		// 返回指定学期的人数统计信息
		// y_term学期代号，为空指当前库,like "1001"
		if (y_term == null || "".equals(y_term.trim()))
			y_term = "";
		else
			y_term = "DUSC" + y_term.trim() + "."; // like "DUSC1001."
		JSONArray jsarr = null;
		String sql = "select COS_NAME,T_SEX,count(PER_ID) T_COUNT ";
		sql += " from " + y_term + "PERSON_INFO_TAB p left join " + y_term
				+ "COURSE_TAB c on p.COS_ID=c.COS_ID ";
		sql += " group by COS_name,T_SEX ";

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DataBaseConnection dbc = null;
		try {
			dbc = new DataBaseConnection();
			pstmt = dbc.getConnection().prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				// 有符合查询条件的记录
				jsarr = new JSONArray();
				do {
					JSONObject jo = new JSONObject();
					jo.put("cos_name", rs.getString("COS_NAME"));
					jo.put("sex", rs.getString("T_SEX"));
					jo.put("count", rs.getString("T_COUNT"));
					jsarr.put(jo);
				} while (rs.next());
			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			throw new Exception("数据库操作失败");
		} finally {
			dbc.close();
		}
		return jsarr;
	}

	public String getDBUsername() throws Exception {
		String un = null;
		try {
			ParseXML pxml = new ParseXML();
			pxml.parse("dbconection.xml");
			un = (String) pxml.getProps().get("user");

		} catch (Exception e) {
			e.printStackTrace();
		}
		return un;
	}

	public JSONArray getSubName(String course) throws Exception {

		JSONArray jsarr = null;
		String sql = "Select s.SUB_NAME,c.COS_NAME,s.SUB_ID,s.SUB_CREDITS FROM SUBJECT_TAB s left join COURSE_TAB c on s.COS_ID=c.COS_ID  ";
		// 为subjectmngpanel提供索引，索引添加sub_id
		// “all”表示返回全部subname
		sql += "all".equals(course) ? " order by s.SUB_NAME"
				: " Where c.COS_NAME='" + course + "' order by s.SUB_NAME";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DataBaseConnection dbc = null;
		try {
			dbc = new DataBaseConnection();
			pstmt = dbc.getConnection().prepareStatement(sql);
			// pstmt.setString(1, member.getUsername());

			rs = pstmt.executeQuery();
			if (rs.next()) {
				// 有符合查询条件的记录
				jsarr = new JSONArray();
				// SQLXML xml = rs.getSQLXML(1);
				// jsarr.put(XML.toJSONObject(xml.toString()));
				do {
					JSONObject jo = new JSONObject();
					jo.put("sub_id", rs.getString("SUB_ID"));
					jo.put("text", rs.getString("SUB_NAME"));
					jo.put("cos", rs.getString("COS_NAME"));
					jo.put("credits", rs.getString("SUB_CREDITS"));
					jsarr.put(jo);
				} while (rs.next());
			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			throw new Exception("数据库操作失败");
		} finally {
			dbc.close();
		}
		return jsarr;
	}

	/**
	 * 得到当前存在的数据库方案，即学期列表，like:'1001','1002','0601'...
	 * 
	 * @return
	 * @throws Exception
	 */
	public JSONArray getSchList() throws Exception {

		JSONArray js = new JSONArray();
		try {
			String curname = DAOFactory.getDatasetDAOInstance().getDBUsername();
			for (int i = 1; i < 40; i++) { // 解析4位数字编号，‘1001,1002,0502,0601’...
				String term = String.format("%1$02d" + "%2$02d", i / 2 + 5,
						i % 2 + 1); // //装配学期，‘0502’开始，term='0501','06'
				int r = DAOFactory.getStudentInstance().ExcuteSQL(
						"select * from dusc" + term + ".PERSON_INFO_TAB",
						"select");
				if (r > -1)
					js.put(new JSONObject("{text:'" + term + "'}"));
				if (curname.indexOf(term) >= 0)
					break;
			}
		} catch (Exception e) {
			throw new Exception("数据库操作失败");
		} finally {

		}
		return js;

	}
}